{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "133b1f26-191c-42d6-a423-9316cab6eac9",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "99e7c542-ef68-4559-ac9f-7357b9646701",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "38db644b-f5af-4026-809c-31b9dd4c7d4a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Date  Price\n",
      "0 2024-01-01    100\n",
      "1 2024-01-03    102\n",
      "2 2024-01-05    101\n",
      "-------------------------------------\n",
      "        Date  Volume\n",
      "0 2024-01-02    2000\n",
      "1 2024-01-04    2100\n",
      "2 2024-01-06    1900\n",
      "-------------------------------------\n",
      "        Date  Price  Volume\n",
      "0 2024-01-01    100     NaN\n",
      "1 2024-01-02    100  2000.0\n",
      "2 2024-01-03    102  2000.0\n",
      "3 2024-01-04    102  2100.0\n",
      "4 2024-01-05    101  2100.0\n",
      "5 2024-01-06    101  1900.0\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 价格数据（日期不连续）\n",
    "df_price = pd.DataFrame({\n",
    "    'Date': pd.date_range('2024-01-01', periods=3, freq='2D'),\n",
    "    'Price': [100, 102, 101]\n",
    "})\n",
    "print(df_price)\n",
    "print(\"-------------------------------------\")\n",
    "# 交易量数据（日期偏移）\n",
    "df_volume = pd.DataFrame({\n",
    "    'Date': pd.date_range('2024-01-02', periods=3, freq='2D'),\n",
    "    'Volume': [2000, 2100, 1900]\n",
    "})\n",
    "\n",
    "print(df_volume)\n",
    "print(\"-------------------------------------\")\n",
    "# 按日期外连接，前向填充缺失值\n",
    "merged = pd.merge_ordered(\n",
    "    df_price, df_volume, on='Date', \n",
    "    how='outer', fill_method='ffill'\n",
    ")\n",
    "print(merged)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8e0875ca-1152-45b9-a40a-67b48639feb6",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aabed307-094f-459f-8dc1-cc717c7f03ed",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2051961d-6951-4938-8567-27358cdbe3c7",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "6a6a3974-4472-4afd-b4b4-a26815f2cccb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           order_time product  order_price\n",
      "0 2024-01-01 09:31:00       A          101\n",
      "1 2024-01-01 09:35:00       B          102\n",
      "-------------------------------------\n",
      "-------------------------------------\n",
      "           quote_time product  market_price\n",
      "0 2024-01-01 09:30:00       A           100\n",
      "1 2024-01-01 09:33:00       B           101\n",
      "-------------------------------------\n",
      "-------------------------------------\n",
      "           order_time product  order_price          quote_time  market_price\n",
      "0 2024-01-01 09:31:00       A          101 2024-01-01 09:30:00           100\n",
      "1 2024-01-01 09:35:00       B          102 2024-01-01 09:33:00           101\n"
     ]
    }
   ],
   "source": [
    "\n",
    "\n",
    "\n",
    "# 订单数据\n",
    "orders = pd.DataFrame({\n",
    "    'order_time': pd.to_datetime(['2024-01-01 09:31', '2024-01-01 09:35']),\n",
    "    'product': ['A', 'B'],\n",
    "    'order_price': [101, 102]\n",
    "})\n",
    "\n",
    "print(orders)\n",
    "print(\"-------------------------------------\")\n",
    "# print(orders.sort_values('order_time'))\n",
    "print(\"-------------------------------------\")\n",
    "# 行情数据\n",
    "quotes = pd.DataFrame({\n",
    "    'quote_time': pd.to_datetime(['2024-01-01 09:30', '2024-01-01 09:33']),\n",
    "    'product': ['A', 'B'],\n",
    "    'market_price': [100, 101]\n",
    "})\n",
    "\n",
    "print(quotes)\n",
    "print(\"-------------------------------------\")\n",
    "# print(quotes.sort_values('quote_time'))\n",
    "print(\"-------------------------------------\")\n",
    "# 按产品分组，向后匹配最近行情\n",
    "merged = pd.merge_asof(\n",
    "    orders.sort_values('order_time'), \n",
    "    quotes.sort_values('quote_time'),\n",
    "    left_on='order_time', \n",
    "    right_on='quote_time',\n",
    "    by='product',  # 按产品分组匹配\n",
    "    direction='backward'\n",
    ")\n",
    "\n",
    "\n",
    "print(merged)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fee3f0af-0943-406b-a794-c20c06ac5678",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1b0b8e44-4ee5-496e-9a90-7a6a242df019",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "04353e7d-acd6-478e-8143-3faa719629f5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           order_time product  order_id\n",
      "0 2024-01-01 09:31:00       A       101\n",
      "1 2024-01-01 09:35:00       B       102\n",
      "2 2024-01-01 09:40:00       A       103\n",
      "-------------------------------------\n",
      "           quote_time product  price\n",
      "0 2024-01-01 09:30:00       A    100\n",
      "1 2024-01-01 09:23:00       B    200\n",
      "2 2024-01-01 09:38:00       A    105\n",
      "-------------------------------------\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 订单数据（左表）\n",
    "orders = pd.DataFrame({\n",
    "    'order_time': pd.to_datetime(['2024-01-01 09:31', '2024-01-01 09:35', '2024-01-01 09:40']),\n",
    "    'product': ['A', 'B', 'A'],\n",
    "    'order_id': [101, 102, 103]\n",
    "})\n",
    "print(orders)\n",
    "print(\"-------------------------------------\")\n",
    "# 行情数据（右表）\n",
    "quotes = pd.DataFrame({\n",
    "    'quote_time': pd.to_datetime(['2024-01-01 09:30', '2024-01-01 09:23', '2024-01-01 09:38']),\n",
    "    'product': ['A', 'B', 'A'],\n",
    "    'price': [100, 200, 105]\n",
    "})\n",
    "print(quotes)\n",
    "print(\"-------------------------------------\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "6baed06e-bf09-43a0-a114-176157d85c19",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_time</th>\n",
       "      <th>product</th>\n",
       "      <th>order_id</th>\n",
       "      <th>quote_time</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2024-01-01 09:31:00</td>\n",
       "      <td>A</td>\n",
       "      <td>101</td>\n",
       "      <td>2024-01-01 09:30:00</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2024-01-01 09:35:00</td>\n",
       "      <td>B</td>\n",
       "      <td>102</td>\n",
       "      <td>2024-01-01 09:33:00</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2024-01-01 09:40:00</td>\n",
       "      <td>A</td>\n",
       "      <td>103</td>\n",
       "      <td>2024-01-01 09:38:00</td>\n",
       "      <td>105</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           order_time product  order_id          quote_time  price\n",
       "0 2024-01-01 09:31:00       A       101 2024-01-01 09:30:00    100\n",
       "1 2024-01-01 09:35:00       B       102 2024-01-01 09:33:00    200\n",
       "2 2024-01-01 09:40:00       A       103 2024-01-01 09:38:00    105"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "merged = pd.merge_asof(\n",
    "    orders.sort_values('order_time'),\n",
    "    quotes.sort_values('quote_time'),\n",
    "    left_on='order_time',\n",
    "    right_on='quote_time',\n",
    "    by='product',\n",
    "    direction='backward'\n",
    ")\n",
    "merged"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "d11ac7ba-779a-4b01-819a-5010b61d8193",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_time</th>\n",
       "      <th>product</th>\n",
       "      <th>order_id</th>\n",
       "      <th>quote_time</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2024-01-01 09:31:00</td>\n",
       "      <td>A</td>\n",
       "      <td>101</td>\n",
       "      <td>2024-01-01 09:30:00</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2024-01-01 09:35:00</td>\n",
       "      <td>B</td>\n",
       "      <td>102</td>\n",
       "      <td>2024-01-01 09:23:00</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2024-01-01 09:40:00</td>\n",
       "      <td>A</td>\n",
       "      <td>103</td>\n",
       "      <td>2024-01-01 09:38:00</td>\n",
       "      <td>105</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           order_time product  order_id          quote_time  price\n",
       "0 2024-01-01 09:31:00       A       101 2024-01-01 09:30:00    100\n",
       "1 2024-01-01 09:35:00       B       102 2024-01-01 09:23:00    200\n",
       "2 2024-01-01 09:40:00       A       103 2024-01-01 09:38:00    105"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "merged = pd.merge_asof(\n",
    "    orders.sort_values('order_time'),\n",
    "    quotes.sort_values('quote_time'),\n",
    "    left_on='order_time',\n",
    "    right_on='quote_time',\n",
    "    by='product',\n",
    "    direction='backward'\n",
    ")\n",
    "merged"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e14ba823-4c00-4c0f-b34c-7aea99ebca90",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5bb6318d-8e48-47eb-a21f-41b6c300c7e6",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "882fbbf2-c92c-4e59-a487-4f6b87f39f5c",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "efdd90a6-1c4a-447d-ab30-41177ede2629",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           order_time product  order_id\n",
      "0 2024-01-01 09:31:00       A       101\n",
      "1 2024-01-01 09:35:00       B       112\n",
      "2 2024-01-01 09:35:00       B       102\n",
      "3 2024-01-01 09:40:00       A       103\n",
      "4 2024-01-01 09:40:00       A       113\n",
      "-------------------------------------\n",
      "           quote_time product  price\n",
      "0 2024-01-01 09:30:00       A    100\n",
      "1 2024-01-01 09:23:00       B    200\n",
      "2 2024-01-01 09:23:00       B    201\n",
      "3 2024-01-01 09:38:00       A    105\n",
      "4 2024-01-01 09:58:00       A    106\n",
      "-------------------------------------\n"
     ]
    }
   ],
   "source": [
    "# import pandas as pd\n",
    "\n",
    "# 订单数据（左表）\n",
    "orders = pd.DataFrame({\n",
    "    'order_time': pd.to_datetime(['2024-01-01 09:31', '2024-01-01 09:35', '2024-01-01 09:35', '2024-01-01 09:40', '2024-01-01 09:40']),\n",
    "    'product': ['A', 'B', 'B', 'A', 'A'],\n",
    "    'order_id': [101, 112, 102, 103, 113]\n",
    "})\n",
    "print(orders)\n",
    "print(\"-------------------------------------\")\n",
    "# 行情数据（右表）\n",
    "quotes = pd.DataFrame({\n",
    "    'quote_time': pd.to_datetime(['2024-01-01 09:30', '2024-01-01 09:23', '2024-01-01 09:23', '2024-01-01 09:38', '2024-01-01 09:58']),\n",
    "    'product': ['A', 'B', 'B', 'A', 'A'],\n",
    "    'price': [100, 200, 201, 105, 106]\n",
    "})\n",
    "print(quotes)\n",
    "print(\"-------------------------------------\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "e8ed4ac3-726b-453f-ba1e-06d5cb449d60",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_time</th>\n",
       "      <th>product</th>\n",
       "      <th>order_id</th>\n",
       "      <th>quote_time</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2024-01-01 09:31:00</td>\n",
       "      <td>A</td>\n",
       "      <td>101</td>\n",
       "      <td>2024-01-01 09:30:00</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2024-01-01 09:35:00</td>\n",
       "      <td>B</td>\n",
       "      <td>112</td>\n",
       "      <td>2024-01-01 09:23:00</td>\n",
       "      <td>201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2024-01-01 09:35:00</td>\n",
       "      <td>B</td>\n",
       "      <td>102</td>\n",
       "      <td>2024-01-01 09:23:00</td>\n",
       "      <td>201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2024-01-01 09:40:00</td>\n",
       "      <td>A</td>\n",
       "      <td>103</td>\n",
       "      <td>2024-01-01 09:38:00</td>\n",
       "      <td>106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2024-01-01 09:40:00</td>\n",
       "      <td>A</td>\n",
       "      <td>113</td>\n",
       "      <td>2024-01-01 09:38:00</td>\n",
       "      <td>106</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           order_time product  order_id          quote_time  price\n",
       "0 2024-01-01 09:31:00       A       101 2024-01-01 09:30:00    100\n",
       "1 2024-01-01 09:35:00       B       112 2024-01-01 09:23:00    201\n",
       "2 2024-01-01 09:35:00       B       102 2024-01-01 09:23:00    201\n",
       "3 2024-01-01 09:40:00       A       103 2024-01-01 09:38:00    106\n",
       "4 2024-01-01 09:40:00       A       113 2024-01-01 09:38:00    106"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged = pd.merge_asof(\n",
    "    orders.sort_values('order_time'),\n",
    "    quotes.sort_values('quote_time'),\n",
    "    left_on='order_time',\n",
    "    right_on='quote_time',\n",
    "    by='product',\n",
    "    direction='backward'\n",
    ")\n",
    "merged"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "69f6d8f9-2770-436a-9bab-058dc8562f8e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_time</th>\n",
       "      <th>product</th>\n",
       "      <th>order_id</th>\n",
       "      <th>quote_time</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2024-01-01 09:31:00</td>\n",
       "      <td>A</td>\n",
       "      <td>101</td>\n",
       "      <td>2024-01-01 09:30:00</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2024-01-01 09:35:00</td>\n",
       "      <td>B</td>\n",
       "      <td>112</td>\n",
       "      <td>2024-01-01 09:23:00</td>\n",
       "      <td>201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2024-01-01 09:35:00</td>\n",
       "      <td>B</td>\n",
       "      <td>102</td>\n",
       "      <td>2024-01-01 09:23:00</td>\n",
       "      <td>201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2024-01-01 09:40:00</td>\n",
       "      <td>A</td>\n",
       "      <td>103</td>\n",
       "      <td>2024-01-01 09:38:00</td>\n",
       "      <td>105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2024-01-01 09:40:00</td>\n",
       "      <td>A</td>\n",
       "      <td>113</td>\n",
       "      <td>2024-01-01 09:38:00</td>\n",
       "      <td>105</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           order_time product  order_id          quote_time  price\n",
       "0 2024-01-01 09:31:00       A       101 2024-01-01 09:30:00    100\n",
       "1 2024-01-01 09:35:00       B       112 2024-01-01 09:23:00    201\n",
       "2 2024-01-01 09:35:00       B       102 2024-01-01 09:23:00    201\n",
       "3 2024-01-01 09:40:00       A       103 2024-01-01 09:38:00    105\n",
       "4 2024-01-01 09:40:00       A       113 2024-01-01 09:38:00    105"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged = pd.merge_asof(\n",
    "    orders.sort_values('order_time'),\n",
    "    quotes.sort_values('quote_time'),\n",
    "    left_on='order_time',\n",
    "    right_on='quote_time',\n",
    "    by='product',\n",
    "    direction='backward',\n",
    "    tolerance=pd.Timedelta(minutes=20)  # 最大允许10分钟时间差\n",
    ")\n",
    "merged"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b1c85bf6-ecd7-4250-80fe-5ab1bb6a5d96",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e3dfcbfc-258c-4572-8138-77c9aa3abb7a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "c6e2c51a-2c4f-41b9-b6c2-77cb6353ba45",
   "metadata": {},
   "source": [
    "# 日期"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "963fb3cc-e9d1-4eea-b1aa-c0fd885a26f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 1. 数据加载与转换\n",
    "df = pd.read_csv('user_activity.csv')\n",
    "df['event_time'] = pd.to_datetime(df['event_time'])\n",
    "\n",
    "# 2. 提取时间特征\n",
    "df['hour'] = df['event_time'].dt.hour\n",
    "df['weekday'] = df['event_time'].dt.dayofweek  # 0=周一, 6=周日\n",
    "\n",
    "# 3. 分析高峰时段\n",
    "peak_hours = df.groupby('hour')['user_id'].nunique().idxmax()  # 用户活跃高峰小时\n",
    "\n",
    "# 4. 筛选周末购买记录\n",
    "weekend_purchases = df[\n",
    "    (df['weekday'].isin([5, 6])) &  # 周六、周日\n",
    "    (df['event_type'] == 'purchase')\n",
    "]\n",
    "\n",
    "# 5. 计算用户首次购买后的复购间隔\n",
    "first_purchase = df.groupby('user_id')['event_time'].min().reset_index()\n",
    "df_merged = df.merge(first_purchase, on='user_id', suffixes=('', '_first'))\n",
    "df_merged['days_to_repeat'] = (df_merged['event_time'] - df_merged['event_time_first']).dt.days"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3aaa19d3-23e7-4574-8696-a1ea7bc4397c",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "628d0b87-10b4-4ac1-bc42-f046dfe5d94d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "d403f200-0a20-49d4-ac90-87753598a26b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  Student  Math  Science\n",
      "0   Alice    90       92\n",
      "1     Bob    85       88\n",
      "-------------------------------------\n",
      "  Student  Subject  Score\n",
      "0   Alice     Math     90\n",
      "1     Bob     Math     85\n",
      "2   Alice  Science     92\n",
      "3     Bob  Science     88\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 创建宽格式数据\n",
    "data = {\n",
    "    'Student': ['Alice', 'Bob'],\n",
    "    'Math': [90, 85],\n",
    "    'Science': [92, 88]\n",
    "}\n",
    "df = pd.DataFrame(data)\n",
    "print(df)\n",
    "print(\"-------------------------------------\")\n",
    "# 转换成长格式\n",
    "melted_df = pd.melt(\n",
    "    df,\n",
    "    id_vars=['Student'],       # 保留学生列\n",
    "    value_vars=['Math', 'Science'], # 转换的科目列\n",
    "    var_name='Subject',        # 新列名：存储科目名称\n",
    "    value_name='Score'         # 新列名：存储分数\n",
    ")\n",
    "\n",
    "print(melted_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "29dc5916-73c6-4073-8984-cf4aa21f7e60",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "b2a591cc-e811-4215-8bc8-9accb514fe86",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    Info Grades        \n",
      "    Name   Math Science\n",
      "0  Alice     90      92\n",
      "1    Bob     85      88\n",
      "-------------------------------------\n"
     ]
    }
   ],
   "source": [
    "# 创建含多级列的数据\n",
    "df_multi = pd.DataFrame({\n",
    "    ('Info', 'Name'): ['Alice', 'Bob'],\n",
    "    ('Grades', 'Math'): [90, 85],\n",
    "    ('Grades', 'Science'): [92, 88]\n",
    "})\n",
    "\n",
    "print(df_multi)\n",
    "print(\"-------------------------------------\")\n",
    "\n",
    "# 指定列层级转换\n",
    "melted_multi = pd.melt(\n",
    "    df_multi,\n",
    "    id_vars=[('Info', 'Name')],\n",
    "    value_vars=[('Grades', 'Math'), ('Grades', 'Science')],\n",
    "    col_level=1,              # 使用第二级列名（Math/Science）\n",
    "    var_name='Subject',\n",
    "    value_name='Score'\n",
    ")\n",
    "\n",
    "print(melted_multi)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5b40e9f1-5bb5-4048-8650-fae57b900320",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ca1ea5bf-52b1-482a-af08-38e3c4a689ca",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "c954135b-a169-4e1f-ae97-817f311739e3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    青年\n",
      "1    青年\n",
      "2    青年\n",
      "3    中年\n",
      "4    中年\n",
      "5    老年\n",
      "6    老年\n",
      "7    老年\n",
      "dtype: category\n",
      "Categories (3, object): ['青年' < '中年' < '老年']\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# 创建示例数据\n",
    "data = pd.Series([18, 22, 25, 30, 35, 40, 45, 50])\n",
    "# 分箱：分成3个等宽区间，并指定标签\n",
    "result = pd.cut(\n",
    "    data, \n",
    "    bins=3, \n",
    "    labels=[\"青年\", \"中年\", \"老年\"],\n",
    "    include_lowest=True  # 确保最小值18被包含\n",
    ")\n",
    "print(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e935b5b7-1820-4435-b098-971bc61a523b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d427712c-35b7-4f38-a63b-0daa5ed2c1a2",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "e3718809-4afe-462e-81c3-c3d87321fab6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>vehicle_id</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>speed</th>\n",
       "      <th>acceleration</th>\n",
       "      <th>braking_force</th>\n",
       "      <th>steering_angle</th>\n",
       "      <th>continuous_driving</th>\n",
       "      <th>location</th>\n",
       "      <th>night_driving</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>102</td>\n",
       "      <td>2023-01-01</td>\n",
       "      <td>75.472060</td>\n",
       "      <td>-0.465906</td>\n",
       "      <td>0.869633</td>\n",
       "      <td>11.094222</td>\n",
       "      <td>1.628275</td>\n",
       "      <td>市区</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>435</td>\n",
       "      <td>2023-01-27</td>\n",
       "      <td>63.045652</td>\n",
       "      <td>-2.997739</td>\n",
       "      <td>0.261838</td>\n",
       "      <td>7.092074</td>\n",
       "      <td>8.379536</td>\n",
       "      <td>高速</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>860</td>\n",
       "      <td>2023-01-12</td>\n",
       "      <td>73.134776</td>\n",
       "      <td>1.668533</td>\n",
       "      <td>0.561717</td>\n",
       "      <td>-14.696779</td>\n",
       "      <td>18.049646</td>\n",
       "      <td>高速</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>270</td>\n",
       "      <td>2023-01-04</td>\n",
       "      <td>89.514422</td>\n",
       "      <td>-1.325766</td>\n",
       "      <td>0.699275</td>\n",
       "      <td>-18.843614</td>\n",
       "      <td>0.988950</td>\n",
       "      <td>市区</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>106</td>\n",
       "      <td>2023-01-30</td>\n",
       "      <td>64.221220</td>\n",
       "      <td>5.614003</td>\n",
       "      <td>0.299589</td>\n",
       "      <td>15.186566</td>\n",
       "      <td>17.039108</td>\n",
       "      <td>高速</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49995</th>\n",
       "      <td>381</td>\n",
       "      <td>2023-01-15</td>\n",
       "      <td>18.177682</td>\n",
       "      <td>-3.431543</td>\n",
       "      <td>0.591230</td>\n",
       "      <td>-14.562768</td>\n",
       "      <td>1.046809</td>\n",
       "      <td>高速</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49996</th>\n",
       "      <td>44</td>\n",
       "      <td>2023-01-07</td>\n",
       "      <td>120.000000</td>\n",
       "      <td>1.958547</td>\n",
       "      <td>0.861104</td>\n",
       "      <td>1.955887</td>\n",
       "      <td>0.141749</td>\n",
       "      <td>国道</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49997</th>\n",
       "      <td>916</td>\n",
       "      <td>2023-01-24</td>\n",
       "      <td>49.901630</td>\n",
       "      <td>0.306843</td>\n",
       "      <td>0.146259</td>\n",
       "      <td>-1.815499</td>\n",
       "      <td>2.017120</td>\n",
       "      <td>国道</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49998</th>\n",
       "      <td>171</td>\n",
       "      <td>2023-01-27</td>\n",
       "      <td>45.385097</td>\n",
       "      <td>-2.429129</td>\n",
       "      <td>0.370914</td>\n",
       "      <td>-5.591587</td>\n",
       "      <td>3.412434</td>\n",
       "      <td>高速</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49999</th>\n",
       "      <td>220</td>\n",
       "      <td>2023-01-02</td>\n",
       "      <td>81.593714</td>\n",
       "      <td>-0.612339</td>\n",
       "      <td>0.556499</td>\n",
       "      <td>7.657142</td>\n",
       "      <td>10.762482</td>\n",
       "      <td>市区</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>50000 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       vehicle_id  timestamp       speed  acceleration  braking_force  \\\n",
       "0             102 2023-01-01   75.472060     -0.465906       0.869633   \n",
       "1             435 2023-01-27   63.045652     -2.997739       0.261838   \n",
       "2             860 2023-01-12   73.134776      1.668533       0.561717   \n",
       "3             270 2023-01-04   89.514422     -1.325766       0.699275   \n",
       "4             106 2023-01-30   64.221220      5.614003       0.299589   \n",
       "...           ...        ...         ...           ...            ...   \n",
       "49995         381 2023-01-15   18.177682     -3.431543       0.591230   \n",
       "49996          44 2023-01-07  120.000000      1.958547       0.861104   \n",
       "49997         916 2023-01-24   49.901630      0.306843       0.146259   \n",
       "49998         171 2023-01-27   45.385097     -2.429129       0.370914   \n",
       "49999         220 2023-01-02   81.593714     -0.612339       0.556499   \n",
       "\n",
       "       steering_angle  continuous_driving location  night_driving  \n",
       "0           11.094222            1.628275       市区              1  \n",
       "1            7.092074            8.379536       高速              1  \n",
       "2          -14.696779           18.049646       高速              0  \n",
       "3          -18.843614            0.988950       市区              0  \n",
       "4           15.186566           17.039108       高速              1  \n",
       "...               ...                 ...      ...            ...  \n",
       "49995      -14.562768            1.046809       高速              0  \n",
       "49996        1.955887            0.141749       国道              0  \n",
       "49997       -1.815499            2.017120       国道              1  \n",
       "49998       -5.591587            3.412434       高速              0  \n",
       "49999        7.657142           10.762482       市区              0  \n",
       "\n",
       "[50000 rows x 9 columns]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from datetime import timedelta\n",
    "\n",
    "# ========== 数据准备 ==========\n",
    "# 模拟1000辆商用车的行驶记录（每辆车30天数据）\n",
    "np.random.seed(42)\n",
    "vehicle_ids = np.arange(1000)\n",
    "dates = pd.date_range(\"2023-01-01\", \"2023-01-30\")\n",
    "\n",
    "# 创建模拟数据集\n",
    "data = pd.DataFrame({\n",
    "    \"vehicle_id\": np.random.choice(vehicle_ids, size=50000),\n",
    "    \"timestamp\": np.random.choice(dates, size=50000),\n",
    "    \"speed\": np.clip(np.random.normal(70, 25, 50000), 0, 120),  # 车速(km/h)\n",
    "    \"acceleration\": np.random.normal(0, 2, 50000),  # 加速度(m/s²)\n",
    "    \"braking_force\": np.random.uniform(0, 1, 50000),  # 刹车力度(0-1)\n",
    "    \"steering_angle\": np.random.normal(0, 15, 50000),  # 方向盘转角(度)\n",
    "    \"continuous_driving\": np.random.exponential(4, 50000),  # 连续驾驶时长(小时)\n",
    "    \"location\": np.random.choice([\"高速\", \"国道\", \"市区\"], 50000),\n",
    "    \"night_driving\": np.random.choice([0, 1], 50000, p=[0.7, 0.3])  # 是否夜间驾驶\n",
    "})\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ba2cbe25-7d27-48ac-bab4-5575dd1abe59",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "652026e6-8e1c-4e4d-bc53-0cbee0dc035e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 添加时间维度\n",
    "data[\"hour\"] = np.random.randint(0, 24, 50000)\n",
    "data[\"is_night\"] = data[\"hour\"].apply(lambda x: 1 if x < 6 or x > 22 else 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "518e2af5-e336-434d-af86-fed3e8e6d2dc",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "f90a3b53-b457-448f-8d5a-2778a3e3b929",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>vehicle_id</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>speed</th>\n",
       "      <th>acceleration</th>\n",
       "      <th>braking_force</th>\n",
       "      <th>steering_angle</th>\n",
       "      <th>continuous_driving</th>\n",
       "      <th>location</th>\n",
       "      <th>night_driving</th>\n",
       "      <th>hour</th>\n",
       "      <th>is_night</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>102</td>\n",
       "      <td>2023-01-01</td>\n",
       "      <td>75.472060</td>\n",
       "      <td>-0.465906</td>\n",
       "      <td>0.869633</td>\n",
       "      <td>11.094222</td>\n",
       "      <td>1.628275</td>\n",
       "      <td>市区</td>\n",
       "      <td>1</td>\n",
       "      <td>11</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>435</td>\n",
       "      <td>2023-01-27</td>\n",
       "      <td>63.045652</td>\n",
       "      <td>-2.997739</td>\n",
       "      <td>0.261838</td>\n",
       "      <td>7.092074</td>\n",
       "      <td>8.379536</td>\n",
       "      <td>高速</td>\n",
       "      <td>1</td>\n",
       "      <td>13</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>860</td>\n",
       "      <td>2023-01-12</td>\n",
       "      <td>73.134776</td>\n",
       "      <td>1.668533</td>\n",
       "      <td>0.561717</td>\n",
       "      <td>-14.696779</td>\n",
       "      <td>18.049646</td>\n",
       "      <td>高速</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>270</td>\n",
       "      <td>2023-01-04</td>\n",
       "      <td>89.514422</td>\n",
       "      <td>-1.325766</td>\n",
       "      <td>0.699275</td>\n",
       "      <td>-18.843614</td>\n",
       "      <td>0.988950</td>\n",
       "      <td>市区</td>\n",
       "      <td>0</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>106</td>\n",
       "      <td>2023-01-30</td>\n",
       "      <td>64.221220</td>\n",
       "      <td>5.614003</td>\n",
       "      <td>0.299589</td>\n",
       "      <td>15.186566</td>\n",
       "      <td>17.039108</td>\n",
       "      <td>高速</td>\n",
       "      <td>1</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49995</th>\n",
       "      <td>381</td>\n",
       "      <td>2023-01-15</td>\n",
       "      <td>18.177682</td>\n",
       "      <td>-3.431543</td>\n",
       "      <td>0.591230</td>\n",
       "      <td>-14.562768</td>\n",
       "      <td>1.046809</td>\n",
       "      <td>高速</td>\n",
       "      <td>0</td>\n",
       "      <td>17</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49996</th>\n",
       "      <td>44</td>\n",
       "      <td>2023-01-07</td>\n",
       "      <td>120.000000</td>\n",
       "      <td>1.958547</td>\n",
       "      <td>0.861104</td>\n",
       "      <td>1.955887</td>\n",
       "      <td>0.141749</td>\n",
       "      <td>国道</td>\n",
       "      <td>0</td>\n",
       "      <td>23</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49997</th>\n",
       "      <td>916</td>\n",
       "      <td>2023-01-24</td>\n",
       "      <td>49.901630</td>\n",
       "      <td>0.306843</td>\n",
       "      <td>0.146259</td>\n",
       "      <td>-1.815499</td>\n",
       "      <td>2.017120</td>\n",
       "      <td>国道</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49998</th>\n",
       "      <td>171</td>\n",
       "      <td>2023-01-27</td>\n",
       "      <td>45.385097</td>\n",
       "      <td>-2.429129</td>\n",
       "      <td>0.370914</td>\n",
       "      <td>-5.591587</td>\n",
       "      <td>3.412434</td>\n",
       "      <td>高速</td>\n",
       "      <td>0</td>\n",
       "      <td>22</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49999</th>\n",
       "      <td>220</td>\n",
       "      <td>2023-01-02</td>\n",
       "      <td>81.593714</td>\n",
       "      <td>-0.612339</td>\n",
       "      <td>0.556499</td>\n",
       "      <td>7.657142</td>\n",
       "      <td>10.762482</td>\n",
       "      <td>市区</td>\n",
       "      <td>0</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>50000 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       vehicle_id  timestamp       speed  acceleration  braking_force  \\\n",
       "0             102 2023-01-01   75.472060     -0.465906       0.869633   \n",
       "1             435 2023-01-27   63.045652     -2.997739       0.261838   \n",
       "2             860 2023-01-12   73.134776      1.668533       0.561717   \n",
       "3             270 2023-01-04   89.514422     -1.325766       0.699275   \n",
       "4             106 2023-01-30   64.221220      5.614003       0.299589   \n",
       "...           ...        ...         ...           ...            ...   \n",
       "49995         381 2023-01-15   18.177682     -3.431543       0.591230   \n",
       "49996          44 2023-01-07  120.000000      1.958547       0.861104   \n",
       "49997         916 2023-01-24   49.901630      0.306843       0.146259   \n",
       "49998         171 2023-01-27   45.385097     -2.429129       0.370914   \n",
       "49999         220 2023-01-02   81.593714     -0.612339       0.556499   \n",
       "\n",
       "       steering_angle  continuous_driving location  night_driving  hour  \\\n",
       "0           11.094222            1.628275       市区              1    11   \n",
       "1            7.092074            8.379536       高速              1    13   \n",
       "2          -14.696779           18.049646       高速              0     5   \n",
       "3          -18.843614            0.988950       市区              0    20   \n",
       "4           15.186566           17.039108       高速              1    22   \n",
       "...               ...                 ...      ...            ...   ...   \n",
       "49995      -14.562768            1.046809       高速              0    17   \n",
       "49996        1.955887            0.141749       国道              0    23   \n",
       "49997       -1.815499            2.017120       国道              1    10   \n",
       "49998       -5.591587            3.412434       高速              0    22   \n",
       "49999        7.657142           10.762482       市区              0    16   \n",
       "\n",
       "       is_night  \n",
       "0             0  \n",
       "1             0  \n",
       "2             1  \n",
       "3             0  \n",
       "4             0  \n",
       "...         ...  \n",
       "49995         0  \n",
       "49996         1  \n",
       "49997         0  \n",
       "49998         0  \n",
       "49999         0  \n",
       "\n",
       "[50000 rows x 11 columns]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "15b48656-27d7-4300-a8dd-14ef03fd12b4",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "54b5daab-56d5-448f-84fd-ec09bf0e5315",
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'data' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[31m---------------------------------------------------------------------------\u001b[39m",
      "\u001b[31mNameError\u001b[39m                                 Traceback (most recent call last)",
      "\u001b[36mCell\u001b[39m\u001b[36m \u001b[39m\u001b[32mIn[2]\u001b[39m\u001b[32m, line 23\u001b[39m\n\u001b[32m     20\u001b[39m     \u001b[38;5;28;01mreturn\u001b[39;00m vehicle_stats\n\u001b[32m     22\u001b[39m \u001b[38;5;66;03m# 执行分析\u001b[39;00m\n\u001b[32m---> \u001b[39m\u001b[32m23\u001b[39m vehicle_behavior = analyze_driving_behavior(\u001b[43mdata\u001b[49m)\n\u001b[32m     24\u001b[39m \u001b[38;5;66;03m# vehicle_behavior\u001b[39;00m\n",
      "\u001b[31mNameError\u001b[39m: name 'data' is not defined"
     ]
    }
   ],
   "source": [
    "# ========== 驾驶行为分析 ==========\n",
    "def analyze_driving_behavior(df):\n",
    "    \"\"\"计算关键驾驶行为指标\"\"\"\n",
    "    # 风险行为标记\n",
    "    df['overspeed'] = np.where(df['speed'] > 90, 1, 0)  # 超速标记\n",
    "    df['hard_brake'] = np.where(df['braking_force'] > 0.8, 1, 0)  # 急刹车\n",
    "    df['sharp_turn'] = np.where(np.abs(df['steering_angle']) > 30, 1, 0)  # 急转弯\n",
    "    df['fatigue_driving'] = np.where(df['continuous_driving'] > 4, 1, 0)  # 疲劳驾驶\n",
    "    \n",
    "    # 聚合车辆级指标\n",
    "    vehicle_stats = df.groupby('vehicle_id').agg(\n",
    "        total_trips=('timestamp', 'count'),\n",
    "        overspeed_rate=('overspeed', 'mean'),\n",
    "        hard_brake_count=('hard_brake', 'sum'),\n",
    "        sharp_turn_count=('sharp_turn', 'sum'),\n",
    "        fatigue_duration=('continuous_driving', 'max'),\n",
    "        night_driving_ratio=('night_driving', 'mean')\n",
    "    ).reset_index()\n",
    "    \n",
    "    return vehicle_stats\n",
    "\n",
    "# 执行分析\n",
    "vehicle_behavior = analyze_driving_behavior(data)\n",
    "# vehicle_behavior"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d9d95d7c-ae56-4d97-8a45-5ad0b6e1d026",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "d8a876bd-3324-404d-88a0-4a10cfcd4740",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>vehicle_id</th>\n",
       "      <th>risk_rating</th>\n",
       "      <th>risk_coeff</th>\n",
       "      <th>risk_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>D</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.543396</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>D</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.362162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>D</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.170732</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>D</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.717073</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>D</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.586364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>995</th>\n",
       "      <td>995</td>\n",
       "      <td>D</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.717647</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>996</th>\n",
       "      <td>996</td>\n",
       "      <td>D</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.783333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>997</th>\n",
       "      <td>997</td>\n",
       "      <td>D</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.532075</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>998</th>\n",
       "      <td>998</td>\n",
       "      <td>D</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.749020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999</th>\n",
       "      <td>999</td>\n",
       "      <td>D</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.776000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1000 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     vehicle_id risk_rating risk_coeff  risk_score\n",
       "0             0           D        2.0    2.543396\n",
       "1             1           D        2.0    2.362162\n",
       "2             2           D        2.0    2.170732\n",
       "3             3           D        2.0    1.717073\n",
       "4             4           D        2.0    3.586364\n",
       "..          ...         ...        ...         ...\n",
       "995         995           D        2.0    1.717647\n",
       "996         996           D        2.0    2.783333\n",
       "997         997           D        2.0    4.532075\n",
       "998         998           D        2.0    2.749020\n",
       "999         999           D        2.0    2.776000\n",
       "\n",
       "[1000 rows x 4 columns]"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# ========== 风险评级模型 ==========\n",
    "def risk_rating_model(df):\n",
    "    \"\"\"基于驾驶行为的风险分级模型\"\"\"\n",
    "    # 特征工程\n",
    "    df['risk_score'] = (\n",
    "        df['overspeed_rate'] * 0.4 +\n",
    "        df['hard_brake_count'] * 0.2 +\n",
    "        df['sharp_turn_count'] * 0.2 +\n",
    "        df['night_driving_ratio'] * 0.2\n",
    "    )\n",
    "    \n",
    "    # 风险分级 (A-D)\n",
    "    bins = [0, 0.15, 0.3, 0.45, float('inf')]\n",
    "    labels = ['A', 'B', 'C', 'D']\n",
    "    df['risk_rating'] = pd.cut(df['risk_score'], bins=bins, labels=labels)\n",
    "    \n",
    "    # 添加风险系数\n",
    "    risk_coefficient = {'A': 1.0, 'B': 1.2, 'C': 1.5, 'D': 2.0}\n",
    "    df['risk_coeff'] = df['risk_rating'].map(risk_coefficient)\n",
    "    \n",
    "    return df[['vehicle_id', 'risk_rating', 'risk_coeff', 'risk_score']]\n",
    "\n",
    "# 执行评级\n",
    "risk_ratings = risk_rating_model(vehicle_behavior)\n",
    "risk_ratings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ba441fbc-33c7-406e-b2e8-b2f49f573f50",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "d41b7df6-f519-4b2b-8885-dd766b528805",
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'vehicle_behavior' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[31m---------------------------------------------------------------------------\u001b[39m",
      "\u001b[31mNameError\u001b[39m                                 Traceback (most recent call last)",
      "\u001b[36mCell\u001b[39m\u001b[36m \u001b[39m\u001b[32mIn[1]\u001b[39m\u001b[32m, line 35\u001b[39m\n\u001b[32m     32\u001b[39m     \u001b[38;5;28;01mreturn\u001b[39;00m df\n\u001b[32m     34\u001b[39m \u001b[38;5;66;03m# 执行UBI模型\u001b[39;00m\n\u001b[32m---> \u001b[39m\u001b[32m35\u001b[39m insurance_data = ubi_insurance_model(\u001b[43mvehicle_behavior\u001b[49m, risk_ratings)\n\u001b[32m     36\u001b[39m \u001b[38;5;66;03m# insurance_data\u001b[39;00m\n",
      "\u001b[31mNameError\u001b[39m: name 'vehicle_behavior' is not defined"
     ]
    }
   ],
   "source": [
    "# ========== UBI保险模型 ==========\n",
    "def ubi_insurance_model(behavior_df, risk_df):\n",
    "    \"\"\"UBI保险定价与干预模型\"\"\"\n",
    "    # 合并数据\n",
    "    df = pd.merge(behavior_df, risk_df, on='vehicle_id')\n",
    "    \n",
    "    # 基础保费计算\n",
    "    base_premium = 5000  # 元/年\n",
    "\n",
    "    \n",
    "    \n",
    "    # 风险调整保费\n",
    "    df['adjusted_premium'] = base_premium * df['risk_coeff'].astype(np.float32)\n",
    "\n",
    "    print(\"--------------11-----------------------------\")\n",
    "    \n",
    "    # 高风险车辆识别\n",
    "    df['high_risk'] = np.where(df['risk_rating'].isin(['C', 'D']), 1, 0)\n",
    "\n",
    "    \n",
    "    \n",
    "    # 模拟干预效果：降低高风险行为5%\n",
    "    intervention_effect = 0.05\n",
    "    df['post_intervention_score'] = np.where(\n",
    "        df['high_risk'] == 1,\n",
    "        df['risk_score'] * (1 - intervention_effect),\n",
    "        df['risk_score']\n",
    "    )\n",
    "\n",
    "    \n",
    "    \n",
    "    return df\n",
    "\n",
    "# 执行UBI模型\n",
    "insurance_data = ubi_insurance_model(vehicle_behavior, risk_ratings)\n",
    "# insurance_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "08e6867c-75ef-4446-8686-5e6f1ff2539a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e189ebb8-01f0-466b-a2f2-6477e2e424a9",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "24ec08d9-d706-4f09-bce6-3c82c8dcae06",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "22d33ab8-fbdc-43a3-9f20-82eb8eecbdd4",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cfd533d5-c47c-4022-a8c9-a11cc2b1218e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "61c6fb42-84f9-4dc5-b4c2-6bb8e2e34e01",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7ed22cb7-291f-42ea-afc2-f4192624cee9",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "80bd7043-8ff1-4c21-953d-9d0b6fe7c024",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.13.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
